﻿;with RestrictedNodes as (
select n.* from medianamik_Node n
inner join medianamik_Type t on t.TypeId = n.typeid
where t.TypeId in (select typeid from medianamik_HierarchicalRule) 
)
select * from RestrictedNodes
inner join medianamik_Node pn on pn.NodeId = RestrictedNodes.ParentNodeId   
inner join medianamik_Type t on t.TypeId = RestrictedNodes.TypeId
inner join medianamik_Type pt on pt.TypeId = pn.TypeId  
LEFT OUTER JOIN medianamik_HierarchicalRule hr on hr.TypeId = t.TypeId AND (hr.ParentItemId = pt.TypeId OR hr.ParentItemId = RestrictedNodes.ParentNodeId )  
where hr.HierarchicalRuleId IS NULL 
And t.IsManyToMany = 0